package com.tnl.base;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class BaseDAO {
	
  private static final Logger logger = LoggerFactory.getLogger(BaseDAO.class);

  /**
   * 结果集为一个对象
   * 
   * @param <T>
   * @param c
   * @param sql
   * @param params
   * @return
   */
  public static <T> T queryForObject(Class<T> c, String sql, Object... params) {
    Connection conn = ConnectionPool.getConnection();
    try {
      ResultSetHandler<T> rsh = new BeanHandler<T>(c);
      QueryRunner qr = new QueryRunner();
      T rtn = (T)qr.query(conn, sql, rsh, params);
      return rtn;
    } catch (SQLException e) {
      logger.error("", e);
      return null;
    } finally {
      ConnectionPool.closeConn(conn);
    }
  }

  public static long getCountBySql(String sql,Object... params){
	  Connection conn = ConnectionPool.getConnection();
	  QueryRunner runner= new QueryRunner();
	  try {
		Long num=(Long) runner.query(conn,sql,new ScalarHandler(),params);
		if(num==null){
			return 0;
		}
		return num.longValue();
	} catch (SQLException e) {
		e.printStackTrace();
		return 0;
	}
	  finally {
	      ConnectionPool.closeConn(conn);
	    }
  }
  
  /**
   * 结果集为对象List
   * 
   * @param <T>
   * @param c
   * @param sql
   * @param params
   * @return
   */
  public static <T> List<T> queryForList(Class<T> c, String sql, Object... params) {
    Connection conn = ConnectionPool.getConnection();
    try {
      ResultSetHandler<List<T>> rsh = new BeanListHandler<T>(c);
      QueryRunner qr = new QueryRunner();
      List<T> rtn = (List<T>)qr.query(conn, sql, rsh, params);
      return rtn;
    } catch (SQLException e) {
      logger.error("", e);
      return null;
    } finally {
      ConnectionPool.closeConn(conn);
    }
  }

  /**
   * 结果集为一行一列的，用于查询记录总数，平均数等
   * 
   * @param sql
   * @param params
   * @return 失败返回null
   */
  public static Object queryOneRowOneCol(String sql, Object... params) {
    Connection conn = ConnectionPool.getConnection();
    try {
      QueryRunner qr = new QueryRunner();
      Object[] array = qr.query(conn, sql, new ArrayHandler(), params);
      if (array == null) {
        return null;
      }
      return array[0];
    } catch (SQLException e) {
      logger.error("", e);
      return null;
    } finally {
      ConnectionPool.closeConn(conn);
    }
  }

  /**
   * 查询一列数据
   * 
   * @param sql
   * @param params
   * @return 失败返回null
   */
  public static List<Object> queryForColumnList(String sql, Object... params) {
    Connection conn = ConnectionPool.getConnection();
    try {
      QueryRunner qr = new QueryRunner();
      List<Object> rtn = (List<Object>)qr.query(conn, sql, new ColumnListHandler(), params);
      return rtn;
    } catch (SQLException e) {
      logger.error("", e);
      return null;
    } finally {
      ConnectionPool.closeConn(conn);
    }
  }

  /**
   * 返回的结果集每个map对应一行记录，key为列名
   * 
   * @param sql
   * @param params
   * @return 失败返回null
   */
  public static List<Map<String, Object>> queryForMap(String sql, Object... params) {
    Connection conn = ConnectionPool.getConnection();
    try {
      QueryRunner qr = new QueryRunner();
      List<Map<String, Object>> rtn = qr.query(conn, sql, new MapListHandler(), params);
      return rtn;
    } catch (SQLException e) {
      logger.error("", e);
      return null;
    } finally {
      ConnectionPool.closeConn(conn);
    }
  }

  /**
   * 执行一条sql，主要是插入、修改、删除
   * 
   * @param sql
   * @param params
   * @return 0 failed, -1 success ,1 SQLException
   */
  public static int execute(String sql, Object... params) {
    Connection conn = ConnectionPool.getConnection();
    try {
      QueryRunner qr = new QueryRunner();
      return qr.update(conn, sql, params);
    } catch (SQLException e) {
      logger.error("", e);
      return -1;
    } finally {
      ConnectionPool.closeConn(conn);
    }
  }

  /*
   * public static Long save(String sql, Object... params) { Long id = null; Connection conn =
   * ConnectionPool.getConnection(); try { QueryRunner qr = new QueryRunner(); qr.update(conn, sql, params);
   * //获取新增记录的自增主键 ScalarHandler<Long> rsh = new ScalarHandler<Long>(1); id = qr.query(conn, "SELECT LAST_INSERT_ID()",
   * rsh);//new ScalarHandler(1) return id; } catch (SQLException e) { logger.error("", e);
   * 
   * } finally { ConnectionPool.closeConn(conn); }
   * 
   * return null; }
   */

  public static Long save(String sql, Object... params) {
    BigInteger id = null;
    Connection conn = ConnectionPool.getConnection();
    try {
      QueryRunner qr = new QueryRunner();
      qr.update(conn, sql, params);
      // 获取新增记录的自增主键
      ScalarHandler<BigInteger> rsh = new ScalarHandler<BigInteger>(1);
      id = qr.query(conn, "SELECT LAST_INSERT_ID()", rsh);// new
      // ScalarHandler(1)
      return id.longValue();
    } catch (SQLException e) {
      logger.error("", e);

    } finally {
      ConnectionPool.closeConn(conn);
    }

    return null;
  }

  public static Long updateone(String sql, Object... params) {
    BigInteger id = null;
    Connection conn = ConnectionPool.getConnection();
    try {
      QueryRunner qr = new QueryRunner();
      int row=qr.update(conn, sql, params);
      System.out.println(row);
      // 获取新增记录的自增主键
      ScalarHandler<BigInteger> rsh = new ScalarHandler<BigInteger>(1);
      id = qr.query(conn, "SELECT LAST_INSERT_ID()", rsh);// new
      // ScalarHandler(1)
      return id.longValue();
    } catch (SQLException e) {
      logger.error("", e);

    } finally {
      ConnectionPool.closeConn(conn);
    }

    return null;
  }

  /**
   * 执行一条sql，主要是插入、修改、删除
   *
   * @param sql
   * @param params
   * @return
   */
  public static long executeInsertGenKey(String sql, Object... params) {
    Connection conn = ConnectionPool.getConnection();
    try {
      PreparedStatement preparedStatement = conn.prepareStatement(sql);
      QueryRunner qr = new QueryRunner();
      qr.fillStatement(preparedStatement, params);
      preparedStatement.executeUpdate();
      ResultSet rs = preparedStatement.getGeneratedKeys();
      long id = rs.next() ? rs.getLong(1) : -1;
      return id;
    } catch (SQLException e) {
      logger.error("", e);
      return -1;
    } finally {
      ConnectionPool.closeConn(conn);
    }
  }

  /**
   * 执行事务
   * 
   * @param map key为sql语句，object[]为对应sql的参�?
   */
  public static void executeTransaction(Map<String, Object[]> map) {
    Connection conn = ConnectionPool.getConnection();
    try {
      conn.setAutoCommit(false);
      Iterator<String> iter = map.keySet().iterator();
      String sql;
      QueryRunner qr;
      Object[] params;
      while (iter.hasNext()) {
        sql = iter.next();
        params = map.get(sql);
        qr = new QueryRunner();
        if (params == null) {
          qr.update(conn, sql);
        } else {
          qr.update(conn, sql, params);
        }
      }
      conn.commit();
    } catch (Exception e) {
      logger.error("", e);
      try {
        conn.rollback();
      } catch (SQLException e1) {
        logger.error("", e1);
      }
    } finally {
      try {
        conn.setAutoCommit(true);
      } catch (Exception e2) {
        logger.error("", e2);
      }
      ConnectionPool.closeConn(conn);
    }
  }

  /**
   * 执行批处理
   * 
   * @param sql
   * @param params
   * @return
   */
  public static int[] batch(String sql, Object[][] params) {
    Connection conn = ConnectionPool.getConnection();
    try {
      QueryRunner qr = new QueryRunner();
      return qr.batch(conn, sql, params);
    } catch (SQLException e) {
      logger.error("", e);
      return new int[] {-1};
    } finally {
      ConnectionPool.closeConn(conn);
    }
  }

  public static int executeTransaction(List<Map<String, Object[]>> list) {
    Connection conn = ConnectionPool.getConnection();
    try {
      conn.setAutoCommit(false);
      for (int i = 0; i < list.size(); i++) {
        Map<String, Object[]> map = list.get(i);
        Iterator<String> iter = map.keySet().iterator();
        String sql;
        QueryRunner qr;
        Object[] params;
        while (iter.hasNext()) {
          sql = iter.next();
          params = map.get(sql);
          qr = new QueryRunner();
          if (params == null) {
            qr.update(conn, sql);
          } else {
            qr.update(conn, sql, params);
          }
        }
      }
      conn.commit();
      return 1;
    } catch (Exception e) {
      e.printStackTrace();
      try {
        conn.rollback();
      } catch (SQLException e1) {
        e1.printStackTrace();
      }
      return -1;
    } finally {
      try {
        conn.setAutoCommit(true);
      } catch (Exception e2) {
        e2.printStackTrace();
      }
      ConnectionPool.closeConn(conn);
    }
  }
}
